package com.example.db;

import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 本工具只做辅助
 * @Classname SqlBuilder
 * @Description TODO
 * @Date 2021/8/13 16:01
 * @Created by X.I.O
 */
public class SqlBuilder {


    private static final String COUNT="SELECT COUNT(1) ";

    private  StringBuffer selectBuffer;

    private  StringBuffer stringBuffer;

    private  StringBuffer pageBuffer;

    private  StringBuffer orderByBuffer;


    /**
     * 标记(为true 时下一次拼接 and 或 or 连接符，会替换成空字符串,替换成功后,会自动变成false)
     */
    private boolean markboolean=false;


    /**
     * 第一次的 and 或 or 连接符 会替换成 WHERE ,替换成功后，会自动变成false
     */
    private boolean whereBoolean=true;



    /**
     * 添加查询sql
     * @param
     * @return
     */
    public SqlBuilder select(String select){
        if(selectBuffer==null){
            selectBuffer=new StringBuffer();
        }
        String sql=selectBuffer.toString().toUpperCase();
        if(!sql.contains("SELECT")){
            selectBuffer.append("SELECT ");
        }else {
            selectBuffer.append(",");
        }
        selectBuffer.append(select);
        return this;
    }


    /**
     * 全部非null 则为false
     * 当有多个有一个为null,则为true,
     * @param args 要判null 的参数
     * @return boolean
     */
    public Boolean manyIsEmty(Object ... args){
        if(args==null){
            return true;
        }
        for (int i = 0; i < args.length; i++) {
            Object obj=args[i];
            if(isEmpty(obj)){
                return true;
            }
        }
        return false;
    }


    public SqlBuilder select(List<String> strings){
        if(selectBuffer==null){
            selectBuffer=new StringBuffer();
        }
        String sql=selectBuffer.toString().toUpperCase();
        if(!sql.contains("SELECT")){
            selectBuffer.append("SELECT ");
        }else {
            selectBuffer.append(",");
        }
        selectBuffer.append(String.join(",",strings));
        return this;
    }




    /**
     *
     * @param field
     * @param obj
     * @param trueObj
     * @param falseObj
     * @return
     */
    public SqlBuilder fieldCaseWhen(String field, Object obj, Object trueObj, Object falseObj){
        selectBuffer.append(", CASE WHEN (").append(field).append("=").append(obj)
                .append(") THEN ").append(trueObj).append(" ELSE ");
        if(falseObj!=null){
            selectBuffer.append(falseObj);
        }else {
            stringBuffer.append(field);
        }
        stringBuffer.append(" END");
        return this;
    }




    public <T> T getTObject(Map<String, Object> objectMap,Class<T> cls){
        T t=null;
        try {
            t=cls.newInstance();
            Field[] fields=cls.getDeclaredFields();
            for (int i = 0; i < fields.length ; i++) {
                Field field=fields[i];
                String key=field.getName();
                Object value=field.get(key);
                field.setAccessible(true);
                field.set(t,value);
            }
            return t;
        }catch (Exception e){
            e.printStackTrace();
        }
        return t;
    }



    public  Boolean isEmpty(Object obj){
        if(obj==null){
            return true;
        }
        if(obj instanceof List){
            return ((List) obj).size()==0;
        }
        if(obj instanceof Map){
            return ((Map) obj).size()==0;
        }
        if(obj instanceof Set){
            return ((Set) obj).size()==0;
        }
        return  obj.toString().length()==0;
    }


    public SqlBuilder selectList(List<String> stringList){
        String select=String.join(",",stringList);
        this.select(select);
        return this;
    }


    /**
     *
     * @param tableName 表名
     * @return SqlBuilder对象
     */
    public SqlBuilder from(String tableName){
        stringBuffer=new StringBuffer();
        stringBuffer.append(" from ").append(tableName);
        return this;
    }

    /**
     * and 连接
     * @param field and连接查询的字段
     * @return
     */
    public SqlBuilder and(String field){
        appendCondition(stringBuffer,"and",field);
        return this;
    }


    /**
     * 配合addMark 使用
     * @return
     */
    public SqlBuilder and(){
        appendCondition(stringBuffer,"and","");
        return this;
    }

    public SqlBuilder or(){
        appendCondition(stringBuffer,"or","");
        return this;
    }

    public SqlBuilder addMark(String mark){
        if(mark!=null && mark.contains("(")){
            this.markboolean=true;
        }
        stringBuffer.append(mark);
        return this;
    }


    /**
     * or 连接
     * @param field
     * @return
     */
    public SqlBuilder or(String field){
        appendCondition(stringBuffer,"or",field);
        return this;
    }


    public SqlBuilder eq(Object obj){
        stringBuffer.append(" = ").append("'").append(obj).append("'");
        return this;
    }

    public SqlBuilder notEq(Object obj){
        stringBuffer.append(" <> ").append("'").append(obj).append("'");
        return this;
    }


    /**
     * like 查询
     * @param obj (查询的对象)
     * @return
     */
    public SqlBuilder like(Object obj){
        stringBuffer.append(" LIKE ").append("'%").append(obj).append("%'");
        return this;
    }

    /**
     * 小于查询
     *  <
     * @param obj
     * @return
     */
    public SqlBuilder lt(Object obj){
        stringBuffer.append(" < ").append("'").append(obj).append("'");
        return this;
    }


    /**
     *
     * 小于等于查询
     * <=
     * @param obj
     * @return
     */
    public SqlBuilder le(Object obj){
        stringBuffer.append(" <= ").append("'").append(obj).append("'");
        return this;
    }

    /**
     * 大于查询
     * @param obj
     * @return
     */
    public SqlBuilder gt(Object obj){
        stringBuffer.append(" > ").append("'").append(obj).append("'");
        return this;
    }


    /**
     * 大于等于查询
     * @param obj
     * @return
     */
    public SqlBuilder ge(Object obj){
        stringBuffer.append(" >= ").append("'").append(obj).append("'");
        return this;
    }


    /**
     * sqlbuilder 的 in查询
     * @param strings
     * @return
     */
    public SqlBuilder in(List<String> strings){
        stringBuffer.append(" IN (");
        int num=strings.size()-1;
        for (int i = 0; i < strings.size() ; i++) {
            String s=strings.get(i);
            if(i!=num){
                stringBuffer.append("'").append(s).append("'").append(",");
            }else {
                stringBuffer.append("'").append(s).append("'");
            }
        }
        stringBuffer.append(")");
        return this;
    }

    public SqlBuilder notIn(List<String> strings){
        stringBuffer.append(" NOT ");
        return in(strings);
    }


    public SqlBuilder groupBy(String ... args){
        List<String> strings= Arrays.asList(args);
        stringBuffer.append(" GROUP BY ");
        String str=String.join(",",strings);
        stringBuffer.append(str);
        return this;
    }


    /**
     * sqlbuilder 的 in 查询
     * @param args
     * @return
     */
    public SqlBuilder in(String ... args){
        List<String> strings= Arrays.asList(args);
        return in(strings);
    }

    /**
     * 右模糊
     * @param obj
     * @return
     */
    public SqlBuilder rightLike(Object obj){
        stringBuffer.append(" LIKE ").append("'").append(obj).append("%'");
        return this;
    }

    public SqlBuilder isNotNull(){
        stringBuffer.append(" IS NOT NULL");
        return this;
    }

    public SqlBuilder isNull(){
        stringBuffer.append(" IS  NULL");
        return this;
    }

    /**
     * 获取查询条数sql
     * @return
     */
    public String getCountSql(){
        return COUNT+stringBuffer.toString();
    }


    public SqlBuilder orderByDesc(Object obj){
        orderByBuffer=new StringBuffer();
        orderByBuffer.append(" ORDER BY ").append(obj).append(" DESC ");
        return this;
    }


    public SqlBuilder orderByAsc(Object obj){
        orderByBuffer=new StringBuffer();
        orderByBuffer.append(" ORDER BY ").append(obj).append(" ASC ");
        return this;
    }


    /**
     * 设置分页sql
     * @param page
     * @param size
     */
    public void page(Integer page,Integer size){
        page=(page-1) * size;
        pageBuffer=new StringBuffer();
        pageBuffer.append(selectBuffer.toString()).append(stringBuffer.toString());
        if(orderByBuffer!=null && orderByBuffer.length()>0){
            pageBuffer.append(orderByBuffer.toString());
        }
        pageBuffer.append(" LIMIT ").append(page).append(",").append(size);
    }

    /**
     * 获取分页sql
     * @return
     */
    public String getPage(){
        if(pageBuffer!=null && pageBuffer.length()!=0){
            return pageBuffer.toString();
        }
        return "";
    }

    /**
     * 获取最终的sql
     * @return
     */
    public String getSql(){
        if(pageBuffer!=null && pageBuffer.length()!=0){
            return pageBuffer.toString();
        }
        String sql=selectBuffer.toString()+stringBuffer.toString();
        if(orderByBuffer!=null){
            sql+=orderByBuffer.toString();
        }
        return sql;
    }

    @Override
    public String toString(){
        selectBuffer.append(stringBuffer.toString());
        if(orderByBuffer!=null && orderByBuffer.length()>0){
            selectBuffer.append(orderByBuffer.toString());
        }
        return selectBuffer.toString();
    }



    public StringBuffer getstringBuffer(){
        return this.selectBuffer;
    }


    public SqlBuilder() {
    }




    private void appendCondition(StringBuffer stringBuffer,String appendCondition,String field){
        //  String sql=stringBuffer.toString().toUpperCase();
        if(this.whereBoolean){
            appendCondition="WHERE";
            this.whereBoolean=false;
        }
        if(this.markboolean){
            appendCondition="";
            this.markboolean=false;
        }
        stringBuffer.append(" ").append(appendCondition)
                .append(" ").append(field);
    }



}
